<?php
namespace haibao\cms\data\behavior;


class SiteMonitor extends \haibao\cms\data\BaseMysql{
	
	public function __construct(){
		parent::__construct('\haibao\cms\model\data\behavior\SiteMonitorCountHour');
		$this->setConfig( \haibao\classlibrary\cms\Config::CLASSLIBRARY_CONFIG_MYSQL_MONITOR );
	}
	
	
	public function getRegionChart($pageId,$regionCode,$startDate,$endDate){
		$data = array();
		$sql = 'select * from ClickRegionDaily where PageId=%s and RegionCode=%s';
		$sql .= ' and Time between %s and %s order by Time';
		$result = $this->query($sql,array($pageId,$regionCode,$startDate,$endDate));
		
		while ($row = $result->fetch_assoc()){
			$data[date('Y-m-d',strtotime($row['Time']))] = $row;
		}
		
		return $data;
	}
	
	public function getPageCountByTime($pageId,$startTime,$endTime,$timeType){
		$data = array();
		$str = '';
		if ($timeType == \haibao\cms\model\data\behavior\PageMonitorCountHour::TYPE_WEEK){
			$str = 'FROM_UNIXTIME((UNIX_TIMESTAMP(Time)-86400*WEEKDAY(Time)),"%Y-%m-%d") as FormatTime';
		} else if ($timeType == \haibao\cms\model\data\behavior\PageMonitorCountHour::TYPE_MONTH){
			$str = 'DATE_FORMAT(Time,"%Y-%m") as FormatTime';
		}
		
		$sql = 'select Type,sum(PV) as PV,sum(RegUserCount) as RegUserCount,sum(IpCount) as IpCount,sum(UV) as UV,PageId,'.$str;
		$sql .= ' from PageMonitorCountHour';
		$sql .= ' where Time Between "'.$startTime.'" and "'.$endTime.'" and PageId='.$pageId.' and Type='.\haibao\cms\model\data\behavior\PageMonitorCount::TYPE_DAY;
		$sql .= ' group by Type,FormatTime order by FormatTime';
		
		$result = $this->query($sql);
		while ($row = $result->fetch_assoc()){
			$model = new \haibao\cms\model\data\behavior\PageMonitorCountHour();
			$model->loadArray($row);
			$model->Time = new \DateTime($result->getData('FormatTime'));
			array_push($data,$model);
		}
		
		return $data;
	}
	
	public function getSiteCountByTime($siteId,$startTime,$endTime,$timeType){
		$data = array();
		$str = '';
		if ($timeType == \haibao\cms\model\data\behavior\PageMonitorCountHour::TYPE_WEEK){
			$str = 'FROM_UNIXTIME((UNIX_TIMESTAMP(Time)-86400*WEEKDAY(Time)),"%Y-%m-%d") as FormatTime';
		} else if ($timeType == \haibao\cms\model\data\behavior\PageMonitorCountHour::TYPE_MONTH){
			$str = 'DATE_FORMAT(Time,"%Y-%m") as FormatTime';
		}
		
		$sql = 'select Type,sum(PV) as PV,sum(UserCount) as UserCount,sum(IpCount) as IpCount,sum(UV) as UV,SiteId,'.$str;
		$sql .= ' from SiteMonitorCountHour';
		$sql .= ' where Time Between "'.$startTime.'" and "'.$endTime.'" and SiteId='.$siteId;
		$sql .= ' group by Type,FormatTime order by FormatTime';
		$result = $this->query($sql);
		while ($row = $result->fetch_assoc()){
			$model = new \haibao\cms\model\data\behavior\SiteMonitorCountHour();
			$model->loadArray($row);
			$model->Time = new \DateTime($result->getData('FormatTime'));
			array_push($data,$model);
		}
		
		return $data;
	}
	
	public function getFeatureCountPerHour($featureId,$startDate,$endDate){
		$data = array();
		$sql = 'select sum(PV) as PV,sum(UV) as UV,sum(IpCount) as IpCount,sum(RegUserCount) as RegUserCount,HOUR(Time) as Hour';
		$sql .= ' from FeatureMonitorCountHour';
		$sql .= ' where FeatureId = %s and Time >%s and Time <%s and Type='.\haibao\cms\model\data\behavior\FeatureMonitorCountHour::TYPE_HOUR;
		$sql .= ' group by Hour order by Hour';		
		$result = $this->query($sql,array($featureId,$startDate,$endDate));
		
		while ($result->fetch_assoc()){
			$model = new \haibao\cms\model\data\behavior\FeatureMonitorCountHour();
			$model->PV = $result->getData('PV');
			$model->UV = $result->getData('UV');
			$model->IpCount = $result->getData('IpCount');
			$model->RegUserCount = $result->getData('RegUserCount');
			$model->Time = $result->getData('Hour');
			array_push($data,$model);
		}
		
		return $data;
	}
	
	public function getFeatureUserPieData($featureId,$startDate,$endDate){
		$data = null;
		$sql = 'select sum(UV) as UV,sum(RegUserCount) as RegUserCount from FeatureMonitorCountHour';
		$sql .= ' where FeatureId=%s and Time >=%s and Time < %s and Type='.\haibao\cms\model\data\behavior\SiteMonitorCountHour::TYPE_DAY;
		$result = $this->query($sql,array($featureId,$startDate,$endDate));
		while ($result->fetch_assoc()){
			$data = new \haibao\cms\model\data\behavior\FeatureMonitorCountHour();
			$data->UV = $result->getData('UV');
			$data->RegUserCount = $result->getData('RegUserCount');
		}
		 
		return $data;
	}
	
	public function getPageUserPieData($pageId,$startDate,$endDate){
	 	$data = null;
	 	$sql = 'select sum(UV) as UV,sum(RegUserCount) as RegUserCount from PageMonitorCountHour';
	 	$sql .= ' where PageId=%s and Time >=%s and Time < %s and Type='.\haibao\cms\model\data\behavior\SiteMonitorCountHour::TYPE_DAY;
	 	$result = $this->query($sql,array($pageId,$startDate,$endDate));
	 	while ($result->fetch_assoc()){
	 		$data = new \haibao\cms\model\data\behavior\PageMonitorCountHour();
	 		$data->UV = $result->getData('UV');
	 		$data->RegUserCount = $result->getData('RegUserCount');
	 	}
	 	
	 	return $data;
	}
	
	public function getRegionAreaPerDay($pageId,$startDate,$endDate){
		$data = array();
		$sql = 'select RegionCode,Count,Time from ClickRegionDaily where PageId=%s';
		$sql .= ' and Time between %s and %s order by Time desc';
		$result = $this->query($sql,array($pageId,$startDate,$endDate));
		$codes = array();
		while ($result->fetch_assoc()){
			$code = $result->getData('RegionCode');
			$time = $result->getData('Time')->format('Y-m-d');
			if (!in_array($code,$codes)){
				array_push($codes,$code);
			}
			
			if (!isset($data[$time])){
				$data[$time] = array();
			}
			
			array_push($data[$time],array('code'=>$code,'count'=>$result->getData('Count')));
		}
		
		if ($codes){
			$sql = 'select Code,Name from UserMonitorRegionDefine where Code in ("'.implode('","',$codes).'") and PageId = %s';
			$this->setConfig(\haibao\classlibrary\cms\Config::CLASSLIBRARY_CONFIG_MYSQL_HAIBAO_CMS);
			$result = $this->query($sql,array($pageId));
			while ($result->fetch_assoc()){
				$code = $result->getData('Code');
				$name = $result->getData('Name');
				foreach ($data as $time=>$value){
					foreach ($value as $k=>$v){
						if ($v['code'] == $code){
							$data[$time][$k]['name'] = $name;
						}
					}
				}
			}
			$this->setConfig(\haibao\classlibrary\cms\Config::CLASSLIBRARY_CONFIG_MYSQL_MONITOR);
			
			foreach ($data as $time=>$value){
				foreach ($value as $k=>$v){
					if (!isset($v['name'])){
						unset($data[$time][$k]);
					}
				}
			}
		}
		
		return $data;
	}
	
	public function getRegionArea($pageId,$startDate,$endDate){
		$data = array();
		$sql = 'select RegionCode,sum(Count) as Count from ClickRegionDaily where PageId =%s';
		$sql .= ' and Time between %s and %s group by RegionCode ORDER BY Count DESC';
		$result = $this->query($sql,array($pageId,$startDate,$endDate));
		$codes = array();
		$arr = array();
		while ($result->fetch_assoc()){
			$code = $result->getData('RegionCode');
			$arr[] = array('code'=>$code,'count'=>$result->getData('Count'));
			array_push($codes,$code);
		}
		
		if ($codes){
			$sql = 'select Code,Name from UserMonitorRegionDefine where Code in ("'.implode('","',$codes).'") and PageId = %s';
			$this->setConfig(\haibao\classlibrary\cms\Config::CLASSLIBRARY_CONFIG_MYSQL_HAIBAO_CMS);
			$result = $this->query($sql,array($pageId));
			while ($result->fetch_assoc()){
				$code = $result->getData('Code');
				$name = $result->getData('Name');
				foreach ($arr as $key=>$value){
					if ($value['code'] == $code){
						$data[$name] = $value;
					}
				}
			}
			$this->setConfig(\haibao\classlibrary\cms\Config::CLASSLIBRARY_CONFIG_MYSQL_MONITOR);
		}
       
		return $data;
	}
	
	public function getExtensionClickRegionArea($pageId,$startDate,$endDate,$str){
	    $data = array();
	    if($str == 'type_extend_site_' && $pageId == 75){
	        $sql = "select RegionCode,sum(Count) as Count from ClickRegionDaily where ";
	        $sql .= " Time between %s and %s and RegionCode like %s and (RegionCode like %s or RegionCode like %s or RegionCode like %s or RegionCode like %s) group by RegionCode ORDER BY Count DESC";
	        $result = $this->query($sql,array($startDate,$endDate,'%_article_%','%code_skyscraper','%code_commenttop','%code_featurebottom','%code_topad'));
	    }elseif($str == 'type_extend_site_' && $pageId == 90){
	        $sql = "select RegionCode,sum(Count) as Count from ClickRegionDaily where ";
	        $sql .= " Time between %s and %s and RegionCode like %s group by RegionCode ORDER BY Count DESC";
	        $result = $this->query($sql,array($startDate,$endDate,'%type_extend_site_toplist_code_skyscraper_channel_%'));
	    }else{
	        $sql = "select RegionCode,sum(Count) as Count from ClickRegionDaily where PageId =%s";
	        $sql .= " and Time between %s and %s and RegionCode like %s group by RegionCode ORDER BY Count DESC";
	        $result = $this->query($sql,array($pageId,$startDate,$endDate,'%'.$str.'%'));
	    }
	    $codes = array();
	    $arr = array();
	    while ($result->fetch_assoc()){
	        $code = $result->getData('RegionCode');
	        $arr[] = array('code'=>$code,'count'=>$result->getData('Count'));
	        array_push($codes,$code);
	    }
	    if ($codes){
	        $this->setConfig(\haibao\classlibrary\cms\Config::CLASSLIBRARY_CONFIG_MYSQL_HAIBAO_CMS);
	        $sql = 'select Code,Name from UserMonitorRegionDefine where Code in ("'.implode('","',$codes).'") and PageId = %s';
	        $result = $this->query($sql,array($pageId));
	        while ($row = $result->fetch_assoc()){
	            $code = $row['Code'];
	            $name = $row['Name'];
	            foreach ($arr as $key=>$value){
	                if ($value['code'] == $code){
	                    $data[$name] = $value;
	                }
	            }
	        }
	        $this->setConfig(\haibao\classlibrary\cms\Config::CLASSLIBRARY_CONFIG_MYSQL_MONITOR);
	    }
	    return $data;
	}
	
	public function getExtensionRegionPer($pageId,$startDate,$endDate,$str){
	    $data = array();
	    if($str == 'type_extend_site_' && $pageId == 75){
	        $sql = "select RegionCode,sum(Count) as Count,Time from ClickRegionDaily where ";
	        $sql .= " Time between %s and %s and RegionCode like %s and (RegionCode like %s or RegionCode like %s or RegionCode like %s or RegionCode like %s) group by RegionCode ORDER BY Count DESC";
	        $result = $this->query($sql,array($startDate,$endDate,'%_article_%','%code_skyscraper','%code_commenttop','%code_featurebottom','%code_topad'));
	    }elseif($str == 'type_extend_site_' && $pageId == 90){
	        $sql = "select RegionCode,sum(Count) as Count,Time from ClickRegionDaily where ";
	        $sql .= " Time between %s and %s and RegionCode like %s group by RegionCode ORDER BY Count DESC";
	        $result = $this->query($sql,array($startDate,$endDate,'%type_extend_site_toplist_code_skyscraper_channel_%'));
	    }else{
	        $sql = "select RegionCode,sum(Count) as Count,Time from ClickRegionDaily where PageId =%s";
	        $sql .= " and Time between %s and %s and RegionCode like %s group by RegionCode ORDER BY Count DESC";
	        $result = $this->query($sql,array($pageId,$startDate,$endDate,'%'.$str.'%'));
	    }
	    $codes = array();
	    $arr = array();
	    while ($result->fetch_assoc()){
	        $code = $result->getData('RegionCode');
			$time = $result->getData('Time')->format('Y-m-d');
			if (!in_array($code,$codes)){
				array_push($codes,$code);
			}
			
			if (!isset($data[$time])){
				$data[$time] = array();
			}
			
			array_push($data[$time],array('code'=>$code,'count'=>$result->getData('Count')));
	    }
	    if ($codes){
	        $this->setConfig(\haibao\classlibrary\cms\Config::CLASSLIBRARY_CONFIG_MYSQL_HAIBAO_CMS);
	        $sql = 'select Code,Name from UserMonitorRegionDefine where Code in ("'.implode('","',$codes).'") and PageId = %s';
	        $result = $this->query($sql,array($pageId));
	        while ($row = $result->fetch_assoc()){
	        $code = $result->getData('Code');
				$name = $result->getData('Name');
				foreach ($data as $time=>$value){
					foreach ($value as $k=>$v){
						if ($v['code'] == $code){
							$data[$time][$k]['name'] = $name;
						}
					}
				}
	        }
	        $this->setConfig(\haibao\classlibrary\cms\Config::CLASSLIBRARY_CONFIG_MYSQL_MONITOR);
	        foreach ($data as $time=>$value){
	            foreach ($value as $k=>$v){
	                if (!isset($v['name'])){
	                    unset($data[$time][$k]);
	                }
	            }
	        }
	    }
	    return $data;
	}
	
	public function getClickCount($pageId,$startDate,$endDate){
		$data = array();
		$sql = 'select X,Y,sum(Count) as Count from ClickCountDaily where PageId=%s';
		$sql .= ' and Time >= %s and Time <= %s group by X,Y order by Count desc limit 2000';
		$result = $this->query($sql,array($pageId,$startDate,$endDate));
		while ($row = $result->fetch_assoc()){
			$arr = array();
			$arr['x'] = $result->getData('X');
			$arr['y'] = $result->getData('Y');
			$arr['value'] = $result->getData('Count');
			array_push($data,$arr);
		}
	
		return $data;
	}
	
	public function getClickCountPerDay($channel,$startDate,$endDate){
		$data = array();
		$sql = 'select sum(Count) as Count,Time from ClickCountDaily where PageId=%s';
		$sql .= ' and Time >= %s and Time <= %s group by Time order by Time ASC';
		$result = $this->query($sql,array($channel,$startDate,$endDate));
		while ($row = $result->fetch_assoc()){
			$model = new \haibao\cms\model\data\behavior\ClickCountDaily();
			$model->Time = $result->getData('Time');
			$model->Count = $result->getData('Count');
			array_push($data,$model);
		}
		
		return $data;
	}
	
	public function getAllPageCount($startTime=null,$endTime=null,$pageIds=null){
		$type = 2;
		if($startTime == date('Y-m-d 00:00:00', time())){
			$type = 1;
		}
		if($pageIds){
			$ids = join(',', $pageIds);
			$sql  = 'select sum(PV) as num,PageId from PageMonitorCountHour where Type = '.$type.' and PageId IN ('.$ids.') and Time >= "'.$startTime.'" and Time <="'.$endTime.'" group by PageId';
		}else{
			$sql  = 'select sum(PV) as num,PageId from PageMonitorCountHour where Type = '.$type.' and Time >= "'.$startTime.'" and Time <="'.$endTime.'" group by PageId';
		}
		
		$data = $this->query($sql);
		$result = array();
		while($data->fetch_assoc()){
			$pageId = $data->getData('PageId');
			$result[$pageId] = $data->getData('num');
		}
		return $result;
	}
}
